﻿CREATE FUNCTION dbo.fs_Batch_NS
	(
		@BatchID int = null,
		@StateID int = null,
		@Access int = 1
	)
RETURNS @IDs TABLE (BatchID int)
AS
BEGIN

IF @StateID IS NULL SELECT @StateID = StatusID FROM t_PurchaseBatch WHERE ID = @BatchID

DECLARE @NSID int SET @NSID = dbo.UserNameSpaceID()

DECLARE @NSs TABLE(NameSpaceID int)
INSERT INTO @NSs SELECT NameSpaceID FROM t_NameSpaces WHERE ParentID = @NSID

DECLARE @StateIDs TABLE(StateID int)
INSERT INTO @StateIDs SELECT ID FROM fs_StateID(@StateID,@Access)

IF @BatchID IS NULL
	INSERT INTO @IDs
	SELECT PB.ID FROM t_PurchaseBatch PB
	INNER JOIN @StateIDs S	ON PB.StatusID = S.StateID
	INNER JOIN @NSs nss	ON PB.NameSpaceID = nss.NameSpaceID
ELSE
	INSERT INTO @IDs
	SELECT PB.ID FROM t_PurchaseBatch PB
	INNER JOIN @StateIDs S ON PB.StatusID = S.StateID
	INNER JOIN @NSs nss	ON PB.NameSpaceID = nss.NameSpaceID
	WHERE (PB.ID = @BatchID)

RETURN

END






